﻿<cfsilent>
	<cfscript>
		
		examId = event.getArg("EX");
		termId = event.getArg("TID");
		
		sql = "SELECT 
				t_student.stu_id, t_student.stu_name, t_student.stu_sex, t_student.idtype, t_student.stu_idcard, 
				t_class.cls_name, t_class.grade, 
				t_subject.sbj_name, t_subject.level_code, t_subject.schooling_length, 
				t_institute.institute_name, 
				t_cet.exam_name, t_cet.exam_charge, 
				entry_prop 
				FROM t_cet_entry 
				INNER JOIN t_cet ON t_cet.exam_id = t_cet_entry.exam_id 
				INNER JOIN t_student ON t_cet_entry.stu_id = t_student.stu_id 
				INNER JOIN t_class ON t_class.cls_id = t_student.cls_id 
				INNER JOIN t_subject ON t_subject.sbj_id = t_class.sbj_id 
				INNER JOIN t_institute ON t_institute.institute_id = t_subject.institute_id 
				WHERE 
					t_cet_entry.term_id = :termId 
					AND 
					t_cet_entry.exam_id = :examId 
					AND 
					t_cet_entry.is_charged = '1' 
				ORDER BY t_student.stu_id ";
		
		queryObj = new Query( datasource=application.dnsSlave );
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="examId", value=examId, cfsqltype="cf_sql_char" );

		rs_student = queryObj.execute( sql=sql ).getResult();


		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( termId & "学期," & examId & "成功报名清单", "utf-8");
		
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("CET报名表", false);
		
		/* 添加CET表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, " ,姓名,性别,学号,证件类型,证件号码,学历,学制,入学年份,年级,院系,专业,班级");
		excel.SpreadsheetAddrow(spreadsheetObj, "填写说明,≤16汉字,男/女,≤20位,1-居民身份证 2-军人及武警人员证件 3-护照 4-其它,身份证必须是15位或18位否则请选其他,1-专科 2-本科 3-研究生,1位数,2位年,2位年,院系名称请注意前后一致,专业名称请注意前后一致,自然班号请注意前后一致");
		excel.SpreadsheetAddrow(spreadsheetObj, "请不要删除前面3行");
		
		row = 4;
		
		for ( a=1; a LTE rs_student.recordCount; a++ ) {
			
			row++;
			
			/* 构建考生数据 */
			rowData = " ," & rs_student["stu_name"][a] & ","; 
			
			/* 转换性别 */
			if ( rs_student["stu_sex"][a] eq "1" ) {
				rowData = rowData & "男,";
			}
			else {
				rowData = rowData & "女,";
			}
			
			rowData = rowData & rs_student["stu_id"][a] & ","; 
			
			/* 转换证件类型 */
			/*
				HB/SFZJLX-2006 《身份证件类型代码》 CET育碧
				01	身份证							1-居民身份证
				02	护照								3-护照
				03	军官证							2-军人及武警人员证件
				04	士兵证							2-军人及武警人员证件
				05	回乡证							4-其它
				06	旅行证							4-其它
				07	临时身份证						4-其它
				08	户口簿							4-其它
				09	警官证							2-军人及武警人员证件
				99	其他								4-其它
			*/
			
			if ( rs_student["idtype"][a] eq "01" ) {
				rowData = rowData & "1,";
			}
			else if ( rs_student["idtype"][a] eq "02" ) {
				rowData = rowData & "3,";
			}
			else if ( rs_student["idtype"][a] eq "03" or rs_student["idtype"][a] eq "04" or rs_student["idtype"][a] eq "09" ) {
				rowData = rowData & "2,";
			}
			else {
				rowData = rowData & "4,";
			}
			
			rowData = rowData & rs_student["stu_idcard"][a] & ",";
			
			/* 转换学历*/	
			/*
				HB/PYCC-2006 培养层次代码
				"1": "博士";					
				"2": "硕士";					3-研究生 
				"3": "本科";					2-本科 
				"4": "专科";					1-专科 
				"9": "其他";
			*/
			if ( rs_student["level_code"][a] eq "2" ) {
				rowData = rowData & "3,";
			}
			else if ( rs_student["level_code"][a] eq "3" ) {
				rowData = rowData & "2,";
			}
			else if ( rs_student["level_code"][a] eq "4" ) {
				rowData = rowData & "1,";
			}
			else {
				rowData = rowData & " ,";
			}
			
			rowData = rowData & int(rs_student["schooling_length"][a]) & ",";

			rowData = rowData & right(rs_student["grade"][a],2) 
							& "," & right(rs_student["grade"][a],2) 
							& "," & rs_student["institute_name"][a]
							& "," & rs_student["sbj_name"][a]
							& "," & rs_student["cls_name"][a];
			
			/* 写入考生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);

		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>